
using System.Web.UI.HtmlControls;
using System;
using System.Configuration;
using System.Drawing;
using System.Data;
using System.Web.UI.WebControls;
using Microsoft.VisualBasic;
using System.Collections;
using System.Web.UI;
using System.Web;


using System.IO;
using System.Text;
//using com.ddsc.database;
using DDSC.OracleADO;
using com.ddsc.common;
using SMAS;
using System.Resources;


namespace smasb2b
{
	public class VNDW0020 : BasePage
	{
		
#region  Web Form 設計工具產生的程式碼
		
		//此為 Web Form 設計工具所需的呼叫。
		[System.Diagnostics.DebuggerStepThrough()]private void InitializeComponent()
		{
			
		}
		protected System.Web.UI.WebControls.Label lblQUERYINVENTORY;
		protected System.Web.UI.WebControls.Label lblDEPT_DATE;
		protected System.Web.UI.WebControls.Label lblCDOE;
		protected System.Web.UI.WebControls.Label lblTO2;
		protected System.Web.UI.WebControls.Label lblTO1;
		
		
		protected System.Web.UI.WebControls.Label lblDES;
		protected System.Web.UI.HtmlControls.HtmlTable Table3;
		protected System.Web.UI.HtmlControls.HtmlTableRow DataGrid_foot;
		protected System.Web.UI.WebControls.Label lblCODE;
		protected System.Web.UI.WebControls.Label lblITEM_NAME;
		protected System.Web.UI.WebControls.Label lblSALE_QTY;
		protected System.Web.UI.WebControls.Label lblINVENTORY_QTY;
		protected System.Web.UI.HtmlControls.HtmlInputImage Image1;
		protected System.Web.UI.WebControls.Label lblMATERIALQUERY;
		protected System.Web.UI.WebControls.Label lblENGLISH_SHORTNAME;
		protected System.Web.UI.WebControls.Label lblCLASS;
		protected System.Web.UI.WebControls.Label lblSTATUS;
		protected ddscControlLibrary.InputBoxCtrl PageIndex;
		protected ddscControlLibrary.InputBoxCtrl PageCount;
		protected ddscControlLibrary.InputBoxCtrl GotoPage;
		protected System.Web.UI.HtmlControls.HtmlTable Table1;
		protected System.Web.UI.HtmlControls.HtmlTable DataGrid;
		protected ddscControlLibrary.InputBoxCtrl txtCODE1;
		protected ddscControlLibrary.InputBoxCtrl txtCODE2;
		protected System.Web.UI.WebControls.Label lblCODE1;
		protected ddscControlLibrary.DateCtrl txtDEPT_DATE1;
		protected ddscControlLibrary.DateCtrl txtDEPT_DATE2;
		protected System.Web.UI.WebControls.Label lblMSG012;
		protected System.Web.UI.HtmlControls.HtmlImage IMG1;
		protected System.Web.UI.WebControls.ImageButton ImageButton1;
		protected System.Web.UI.WebControls.ImageButton BtnNext;
		protected System.Web.UI.WebControls.ImageButton btnPREV;
		protected System.Web.UI.WebControls.ImageButton BtnGo;
		protected ddscControlLibrary.SubmitButtonCtrl btnSearch;
		protected System.Web.UI.WebControls.Label lblCODE_ERR;
		protected System.Web.UI.WebControls.Label lblDEPT_DATE_ERR;
		protected System.Web.UI.WebControls.Label lblPAGEINDEX;
		protected System.Web.UI.WebControls.Label lblTOTAL;
		protected System.Web.UI.WebControls.Label lblPAGE1;
		protected System.Web.UI.WebControls.Label lblPAGE;
		protected System.Web.UI.WebControls.Label lblCODE2;
		protected System.Web.UI.WebControls.Label lblCODE3;
		protected System.Web.UI.WebControls.Label lblJUNK_QTY;
		protected System.Web.UI.WebControls.Label lblEXP_INV_QTY;
		protected System.Web.UI.WebControls.Label lblEXP_JUNK_QTY;
		protected System.Web.UI.WebControls.Label lblPRINTPAGE;
		protected System.Web.UI.HtmlControls.HtmlInputHidden hidVENDOR;
		protected System.Web.UI.WebControls.Label lblBP_QTY;
		protected System.Web.UI.WebControls.Label lblST_QTY;
		protected System.Web.UI.WebControls.Label lblPREN_QTY;
		protected System.Web.UI.WebControls.Label lblCLASS_A;
		protected System.Web.UI.WebControls.Label lblINVENTORY_QTY1;
		
		//注意: 下列預留位置宣告是 Web Form 設計工具需要的項目。
		//請勿刪除或移動它。
		private System.Object designerPlaceholderDeclaration;
		
		
		private void Page_Init(System.Object sender, System.EventArgs e)
		{
			//CODEGEN: 此為 Web Form 設計工具所需的方法呼叫
			//請勿使用程式碼編輯器進行修改。
			InitializeComponent();
		}
		
#endregion
		BasePage BasePage = new BasePage();
		
		private void Page_Load(System.Object sender, System.EventArgs e)
		{
			//在這裡放置使用者程式碼以初始化網頁
			if (IsReSubmit)
			{
				DataGrid_DataBind();
			}
			
			this.BtnGo.Enabled = false;
			this.BtnNext.Enabled = false;
			this.btnPREV.Enabled = false;
			
			VENDOR loginUser = GetLoginUser();
			if (!(loginUser == null))
			{
				hidVENDOR.Value = loginUser.VENDOR_NO;
			}

            this.BtnGo.Click+=BtnGo_Click;
            this.BtnNext.Click+=BtnNext_Click;
            this.btnPREV.Click+=btnPREV_Click;
            this.btnSearch.Click+=btnSearch_Click;
			
		}
		//Private Sub btnFIRST_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFIRST.Click
		//    PageIndex.Text = 1
		//    DataGrid_DataBind()
		//End Sub
		
		//Private Sub btnPREV_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPREV.Click
		//    PageIndex.Text = CStr(CInt(PageIndex.Text) - 1)
		//    DataGrid_DataBind()
		//End Sub
		
		//Private Sub btnNEXT_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNEXT.Click
		//    PageIndex.Text = CStr(CInt(PageIndex.Text) + 1)
		//    DataGrid_DataBind()
		//End Sub
		
		//Private Sub btnLAST_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLAST.Click
		//    PageIndex.Text = CStr(PageCount.Text)
		//    DataGrid_DataBind()
		//End Sub
		
		//Private Sub btnGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGo.Click
		//    PageIndex.Text = CStr(GotoPage.Text)
		//    DataGrid_DataBind()
		//End Sub
		
		public void DataGrid_DataBind()
		{
			//變數宣告
			VENDOR LoginUser = GetLoginUser();
			DataSet ds = default(DataSet);
			dbAccess dba = new dbAccess();
            ArrayList m_arrParam = new ArrayList();
			string strCondition = "";
			int i = 0;
			int count = 1;
			string[] str_SQL_a = new string[count + 1];
			string[] str_SQL_b = new string[count + 1];
			
			try
			{
				//目前頁次指定Goto
				GotoPage.Text = Server.HtmlEncode(PageIndex.Text);
				
				//預設查詢語法
				
				//Dim SqlCommandText = "SELECT A.CODE,B.ITEM_NAME,A.SALE_QTY,B.INVENTORY_QTY FROM " _
				//                   + " (SELECT A.CODE,NVL(SUM(A.SALE_QTY_UPD),0)SALE_QTY  " _
				//                   + " FROM VND_INVENTORY_SUMMARY A WHERE  A.DEPT_DATE BETWEEN '" & Me.txtDEPT_DATE1.Text & "' AND '" & Me.txtDEPT_DATE2.Text & "' GROUP BY A.CODE)A  " _
				//                   + " ,(SELECT A.CODE,A.ITEM_NAME,B.INVENTORY_QTY FROM  " _
				//                   + " (SELECT A.CODE,DECODE(B.ENGLISH_SHORTNAME,null,B.CHINESE_SHORTNAME,B.ENGLISH_SHORTNAME) ITEM_NAME FROM " _
				//                   + " (SELECT CODE FROM VND_SUPPLY WHERE VENDOR_NO='" & LoginUser.VENDOR_NO & "')A,BAS_MATERIAL B WHERE " _
				//                   + " A.CODE=B.CODE(+) and b.material_type='S'  " _
				//                   + " and b.code not in (select code from BAS_MATERIAL where VENDOR_NO='" & LoginUser.VENDOR_NO & "'and  class='G' and subclass='7'))A, " _
				//                   + " (SELECT CODE,NVL(SUM(ABS(CURR_QTY)),0) AS INVENTORY_QTY FROM INV_INVENTORY where store_no in(select store_no from BAS_STORE where store_type='1' and sign_flag='Y') GROUP BY CODE)B " _
				//                   + " WHERE A.CODE=B.CODE(+))B WHERE B.CODE=A.CODE(+) AND A.CODE IS NOT NULL "
				
				//P3511M0179-B2B平台廠商使用銷售庫存量線上查詢,開放報表給前台列印,WALTER,20111128
				//Dim SqlCommandText = "SELECT V_SUPPY.CODE CODE3,V_SUPPY.ITEM_NAME,NVL(V_SALE.SALE_QTY,0)SALE_QTY,NVL(V_INV.INV_QTY,0)INVENTORY_QTY,NVL(V_JUNK.JUNK_QTY,0)JUNK_QTY,NVL(V_EXP.EXP_INV_QTY,0)EXP_INV_QTY,NVL(V_EXP.EXP_JUNK_QTY,0)EXP_JUNK_QTY FROM " _
				//20111214,新增網上精品註記
				//Dim SqlCommandText As String = "SELECT V_SUPPY.CODE CODE3,V_SUPPY.ITEM_NAME,NVL(V_SALE.SALE_QTY,0)SALE_QTY,NVL(V_INV.INV_QTY,0)INVENTORY_QTY,NVL(V_JUNK.JUNK_QTY,0)JUNK_QTY,NVL(V_EXP.EXP_INV_QTY,0)EXP_INV_QTY,NVL(V_EXP.EXP_JUNK_QTY,0)EXP_JUNK_QTY,NVL (V_BP.TRANS_QTY, 0) BP_QTY,NVL (V_ST.TRANS_QTY, 0) ST_QTY,NVL (V_PREN.PREN_QTY, 0) PREN_QTY FROM " _
				//Dim SqlCommandText As String = "SELECT V_SUPPY.CLASS_A,V_SUPPY.CODE3,V_SUPPY.ITEM_NAME,V_SUPPY.sale_qty,V_SUPPY.INVENTORY_QTY1,V_SUPPY.JUNK_QTY,V_SUPPY.EXP_INV_QTY,"
				
				//20111222,MODI WALTER,改抓法NEW CODE
				//Dim SqlCommandText As String = "SELECT V_SUPPY.*,"
				//SqlCommandText += " NVL (V_EXP.EXP_JUNK_QTY, 0) EXP_JUNK_QTY,"
				//SqlCommandText += " NVL (V_BP.TRANS_QTY, 0) BP_QTY,"
				//SqlCommandText += " NVL (V_ST.TRANS_QTY, 0) ST_QTY,"
				//SqlCommandText += " NVL (V_PREN.PREN_QTY, 0) PREN_QTY"
				//SqlCommandText += " FROM ("
				//SqlCommandText += " SELECT DECODE(CLASS,'A','',DECODE(SUBCLASS,'A','A','')) CLASS_A,A.CODE CODE3,DECODE(A.ENGLISH_SHORTNAME,null,A.ENGLISH_SHORTNAME,A.ENGLISH_SHORTNAME) ITEM_NAME,A.sale_qty ,NVL(C.CURR_QTY,0) INVENTORY_QTY1 ,NVL(D.CURR_QTY,0) JUNK_QTY,B.CURR_QTY EXP_INV_QTY"
				//SqlCommandText += " FROM "
				//SqlCommandText += " (SELECT NVL(A.SALE_QTY,0) + NVL(po.sale_qty, 0) sale_qty, A.AMOUNT,B.CODE,B.CLASS,B.SUBCLASS,NVL(B.ENGLISH_SHORTNAME,'') ENGLISH_SHORTNAME,NVL(B.STORE_UNIT,'') STORE_UNIT,D.VENDOR_NO,NVL(D.VENDOR_CHINESENAME,D.VENDOR_ENGLISHNAME) VENDOR,NVL(D.CLERK,'') || '/' || NVL(D.CLERK_ADDRESS,'') CLERK,NVL(D.FAX1,'') FAX1"
				//SqlCommandText += " FROM (SELECT CODE, SUM(SALE_QTY_UPD) SALE_QTY,SUM(sale_tot_amt) AMOUNT"
				//SqlCommandText += " FROM CRW_FLIGHT_SALE WHERE"
				//SqlCommandText += " DEPT_DATE between '" + Me.txtDEPT_DATE1.Text + "' And '" + Me.txtDEPT_DATE2.Text + "' AND SALE_QTY_UPD > 0 GROUP BY CODE) A,MATERIAL B,SUPPLY C,VENDOR D,"
				//SqlCommandText += " (SELECT PD.CODE, NVL (SUM (PD.ORDER_QTY), 0) sale_qty from  PRE_ORDER_MASTER PM , PRE_ORDER_DETAILS PD WHERE PM.DEPT_DATE BETWEEN '" + Me.txtDEPT_DATE1.Text + "' AND '" + Me.txtDEPT_DATE2.Text + "' AND PM.ORDER_NO = PD.ORDER_NO(+) AND PM.STATUS >='8' AND PM.RTN_FLAG ='S' GROUP BY PD.code) po"
				//SqlCommandText += " WHERE C.CODE=A.CODE(+) AND B.CODE(+)=C.CODE AND C.VENDOR_NO =D.VENDOR_NO AND C.CODE = po.CODE(+) and C.VENDOR_NO='" + LoginUser.VENDOR_NO + "'"
				//SqlCommandText += " AND (A.SALE_QTY > 0 OR po.sale_qty>0 OR B.CURR_QTY>0)"
				//SqlCommandText += " ) A,"
				//SqlCommandText += " (SELECT CODE,SUM(CURR_QTY) CURR_QTY FROM INV_STOCK_MONTH_END WHERE BACKUP_PERIOD = '" + Me.txtDEPT_DATE2.Text.Substring(0, 6) + "' AND STORE_NO<>'AA' AND STORE_NO<>'UNI' GROUP BY CODE) B, "
				//SqlCommandText += " (SELECT CODE,SUM(CURR_QTY) CURR_QTY FROM INV_INVENTORY Where STORE_NO<> 'AA' GROUP BY CODE) C,"
				//SqlCommandText += " (SELECT CODE,SUM(CURR_QTY) CURR_QTY FROM INV_INVENTORY Where STORE_NO = 'TEMO'  GROUP BY CODE) D"
				//SqlCommandText += " WHERE A.CODE=B.CODE(+) AND A.CODE=C.CODE(+) AND A.CODE=D.CODE(+)"
				//SqlCommandText += " ) V_SUPPY,"
				//'V_EXP
				//SqlCommandText += " (SELECT CODE, "
				//SqlCommandText += " NVL (EXP_JUNK_QTY, 0) EXP_JUNK_QTY "
				//SqlCommandText += " FROM VND_INVENTORY_SUMMARY "
				//SqlCommandText += " WHERE DEPT_DATE = '" + Me.txtDEPT_DATE2.Text + "') V_EXP,"
				//'V_BP
				//SqlCommandText += " (  SELECT D.CODE,"
				//SqlCommandText += " SUM(DECODE (CARD_ID, 'B', TRANS_QTY, 'P', TRANS_QTY, 'J', -1 * TRANS_QTY, -1 * TRANS_QTY)) TRANS_QTY "
				//SqlCommandText += " FROM    (SELECT *"
				//SqlCommandText += " FROM INV_TRANS_MASTER "
				//SqlCommandText += " WHERE     CONFIRM_DATE >= '" + Me.txtDEPT_DATE1.Text + "'"
				//SqlCommandText += " AND CONFIRM_DATE <= '" + Me.txtDEPT_DATE2.Text + "'"
				//SqlCommandText += " AND CARD_ID IN ('B', 'P', 'J', 'Q') "
				//SqlCommandText += " AND STORE_FROM IN ('MANO', 'MANG')) M "
				//SqlCommandText += " left Join"
				//SqlCommandText += " INV_TRANS_DETAILS D "
				//SqlCommandText += " ON M.TRANS_NO = D.TRANS_NO "
				//SqlCommandText += " GROUP BY D.CODE) V_BP,"
				//'V_ST
				//SqlCommandText += " (  SELECT D.CODE,"
				//SqlCommandText += " SUM (DECODE (CARD_ID, 'G', TRANS_QTY, -1 * TRANS_QTY)) TRANS_QTY"
				//SqlCommandText += " FROM    (SELECT *"
				//SqlCommandText += " FROM INV_TRANS_MASTER "
				//SqlCommandText += " WHERE     CONFIRM_DATE >= '" + Me.txtDEPT_DATE1.Text + "'"
				//SqlCommandText += " AND CONFIRM_DATE <= '" + Me.txtDEPT_DATE2.Text + "'"
				//SqlCommandText += " AND CARD_ID IN ('G') "
				//SqlCommandText += " AND STORE_FROM IN ('APN') "
				//SqlCommandText += " AND REMARK LIKE '短少報告書%') M "
				//SqlCommandText += " left Join "
				//SqlCommandText += " INV_TRANS_DETAILS D "
				//SqlCommandText += " ON M.TRANS_NO = D.TRANS_NO"
				//SqlCommandText += " GROUP BY CODE) V_ST, "
				//'V_PREN
				//SqlCommandText += " (SELECT CODE, PREN_QTY"
				//SqlCommandText += " FROM INV_INVENTORY"
				//SqlCommandText += " WHERE STORE_NO IN ('APN')) V_PREN         "
				//'WHERE
				//SqlCommandText += " WHERE V_SUPPY.CODE3=V_EXP.CODE(+)"
				//SqlCommandText += " AND V_SUPPY.CODE3=V_BP.CODE(+)"
				//SqlCommandText += " AND V_SUPPY.CODE3=V_ST.CODE(+)"
				//SqlCommandText += " AND V_SUPPY.CODE3=V_PREN.CODE(+)"
				//'SqlCommandText += " ORDER BY V_MAIN.CODE             "
				
				//20111222,MODI WALTER,改抓法BK OLD CODE
				//string SqlCommandText = "SELECT DECODE(CLASS,\'A\',\'\',DECODE(SUBCLASS,\'A\',\'A\',\'\')) CLASS_A,V_SUPPY.CODE CODE3,V_SUPPY.ITEM_NAME,NVL(V_SALE.SALE_QTY,0)SALE_QTY,NVL(V_INV.INV_QTY,0)INVENTORY_QTY1,NVL(V_JUNK.JUNK_QTY,0)JUNK_QTY,NVL(V_EXP.EXP_INV_QTY,0)EXP_INV_QTY,NVL(V_EXP.EXP_JUNK_QTY,0)EXP_JUNK_QTY,NVL (V_BP.TRANS_QTY, 0) BP_QTY,NVL (V_ST.TRANS_QTY, 0) ST_QTY,NVL (V_PREN.PREN_QTY, 0) PREN_QTY FROM " + "(SELECT A.CODE,DECODE(B.ENGLISH_SHORTNAME,null,B.CHINESE_SHORTNAME,B.ENGLISH_SHORTNAME) ITEM_NAME,CLASS,SUBCLASS FROM (SELECT CODE FROM VND_SUPPLY WHERE VENDOR_NO=\'" + LoginUser.VENDOR_NO + "\')A,BAS_MATERIAL B WHERE B.MATERIAL_TYPE=\'S\' AND B.CODE NOT IN (SELECT CODE FROM BAS_MATERIAL WHERE VENDOR_NO=\'" + LoginUser.VENDOR_NO + "\' AND  CLASS=\'G\' AND SUBCLASS=\'7\') and A.CODE=B.CODE(+)) V_SUPPY," + "(SELECT A.CODE,NVL(SUM(A.SALE_QTY_UPD),0)SALE_QTY FROM VND_INVENTORY_SUMMARY A WHERE  A.DEPT_DATE BETWEEN \'" + this.txtDEPT_DATE1.Text + "\' AND \'" + this.txtDEPT_DATE2.Text + "\' GROUP BY A.CODE)V_SALE , " + "(SELECT CODE,NVL(EXP_INV_QTY,0)EXP_INV_QTY,NVL(EXP_JUNK_QTY,0)EXP_JUNK_QTY FROM VND_INVENTORY_SUMMARY WHERE DEPT_DATE=\'" + this.txtDEPT_DATE2.Text + "\') V_EXP, " + "(SELECT CODE,NVL(SUM(ABS(CURR_QTY)),0) AS INV_QTY FROM INV_INVENTORY WHERE STORE_NO <> ALL(\'TEMG\',\'TEMO\',\'STOR\',\'AA\',\'UNI\') GROUP BY CODE) V_INV, " + "(SELECT CODE,NVL(SUM(ABS(CURR_QTY)),0) AS JUNK_QTY FROM INV_INVENTORY WHERE STORE_NO IN (\'TEMG\',\'TEMO\', \'STOR\') GROUP BY CODE) V_JUNK " + ",(SELECT D.CODE,SUM(DECODE(CARD_ID,\'B\',TRANS_QTY ,\'P\',TRANS_QTY,\'J\',-1*TRANS_QTY,-1*TRANS_QTY)) TRANS_QTY FROM (SELECT * FROM INV_TRANS_MASTER WHERE CONFIRM_DATE >=\'" + this.txtDEPT_DATE1.Text + "\' AND CONFIRM_DATE <=\'" + this.txtDEPT_DATE2.Text + "\' AND CARD_ID IN (\'B\',\'P\',\'J\',\'Q\')  AND STORE_FROM IN (\'MANO\',\'MANG\')) M LEFT JOIN INV_TRANS_DETAILS D ON M.TRANS_NO=D.TRANS_NO GROUP BY D.CODE) V_BP " + ",(SELECT D.CODE,SUM(DECODE(CARD_ID,\'G\',TRANS_QTY ,-1*TRANS_QTY)) TRANS_QTY FROM (SELECT * FROM INV_TRANS_MASTER WHERE CONFIRM_DATE >=\'"
				//+ this.txtDEPT_DATE1.Text + "\' AND CONFIRM_DATE <=\'" + this.txtDEPT_DATE2.Text + "\' AND CARD_ID IN (\'G\')  AND STORE_FROM IN (\'APN\')   AND REMARK LIKE \'短少報告書%\') M LEFT JOIN INV_TRANS_DETAILS D ON M.TRANS_NO=D.TRANS_NO GROUP BY CODE) V_ST " + ",(SELECT CODE,PREN_QTY FROM INV_INVENTORY WHERE STORE_NO IN (\'APN\')) V_PREN " + " WHERE V_SUPPY.CODE IS NOT NULL  AND V_SUPPY.CODE=V_SALE.CODE(+) AND V_SUPPY.CODE=V_EXP.CODE(+) AND V_SUPPY.CODE = V_INV.CODE(+) AND V_SUPPY.CODE = V_JUNK.CODE(+) AND (SALE_QTY <> 0 OR INV_QTY<> 0 OR JUNK_QTY<>0 OR EXP_INV_QTY<>0 OR EXP_JUNK_QTY<>0) " + " AND V_SUPPY.CODE = V_BP.CODE(+) AND V_SUPPY.CODE = V_ST.CODE(+) AND V_SUPPY.CODE = V_PREN.CODE(+) ";
				//----20111222 -END
                //Ken
                string SqlCommandText = "SELECT DECODE(CLASS,\'A\',\'\',DECODE(SUBCLASS,\'A\',\'A\',\'\')) CLASS_A,V_SUPPY.CODE CODE3,V_SUPPY.ITEM_NAME,NVL(V_SALE.SALE_QTY,0)SALE_QTY,NVL(V_INV.INV_QTY,0)INVENTORY_QTY1,NVL(V_JUNK.JUNK_QTY,0)JUNK_QTY,NVL(V_EXP.EXP_INV_QTY,0)EXP_INV_QTY,NVL(V_EXP.EXP_JUNK_QTY,0)EXP_JUNK_QTY,NVL (V_BP.TRANS_QTY, 0) BP_QTY,NVL (V_ST.TRANS_QTY, 0) ST_QTY,NVL (V_PREN.PREN_QTY, 0) PREN_QTY FROM " + "(SELECT A.CODE,DECODE(B.ENGLISH_SHORTNAME,null,B.CHINESE_SHORTNAME,B.ENGLISH_SHORTNAME) ITEM_NAME,CLASS,SUBCLASS FROM (SELECT CODE FROM VND_SUPPLY WHERE VENDOR_NO=:VENDORNO)A,BAS_MATERIAL B WHERE B.MATERIAL_TYPE=\'S\' AND B.CODE NOT IN (SELECT CODE FROM BAS_MATERIAL WHERE VENDOR_NO=:VENDORNO AND  CLASS=\'G\' AND SUBCLASS=\'7\') and A.CODE=B.CODE(+)) V_SUPPY," + "(SELECT A.CODE,NVL(SUM(A.SALE_QTY_UPD),0)SALE_QTY FROM VND_INVENTORY_SUMMARY A WHERE  A.DEPT_DATE BETWEEN :DEPTDATE1 AND :DEPTDATE2 GROUP BY A.CODE)V_SALE , (SELECT CODE,NVL(EXP_INV_QTY,0)EXP_INV_QTY,NVL(EXP_JUNK_QTY,0)EXP_JUNK_QTY FROM VND_INVENTORY_SUMMARY WHERE DEPT_DATE=:DEPTDATE2) V_EXP, (SELECT CODE,NVL(SUM(ABS(CURR_QTY)),0) AS INV_QTY FROM INV_INVENTORY WHERE STORE_NO <> ALL(\'TEMG\',\'TEMO\',\'STOR\',\'AA\',\'UNI\') GROUP BY CODE) V_INV, " + "(SELECT CODE,NVL(SUM(ABS(CURR_QTY)),0) AS JUNK_QTY FROM INV_INVENTORY WHERE STORE_NO IN (\'TEMG\',\'TEMO\', \'STOR\') GROUP BY CODE) V_JUNK ,(SELECT D.CODE,SUM(DECODE(CARD_ID,\'B\',TRANS_QTY ,\'P\',TRANS_QTY,\'J\',-1*TRANS_QTY,-1*TRANS_QTY)) TRANS_QTY FROM (SELECT * FROM INV_TRANS_MASTER WHERE CONFIRM_DATE >=:DEPTDATE1 AND CONFIRM_DATE <=:DEPTDATE2 AND CARD_ID IN (\'B\',\'P\',\'J\',\'Q\')  AND STORE_FROM IN (\'MANO\',\'MANG\')) M LEFT JOIN INV_TRANS_DETAILS D ON M.TRANS_NO=D.TRANS_NO GROUP BY D.CODE) V_BP ,(SELECT D.CODE,SUM(DECODE(CARD_ID,\'G\',TRANS_QTY ,-1*TRANS_QTY)) TRANS_QTY FROM (SELECT * FROM INV_TRANS_MASTER WHERE CONFIRM_DATE >=:DEPTDATE1 AND CONFIRM_DATE <=:DEPTDATE2 AND CARD_ID IN (\'G\')  AND STORE_FROM IN (\'APN\')   AND REMARK LIKE \'短少報告書%\') M LEFT JOIN INV_TRANS_DETAILS D ON M.TRANS_NO=D.TRANS_NO GROUP BY CODE) V_ST " + ",(SELECT CODE,PREN_QTY FROM INV_INVENTORY WHERE STORE_NO IN (\'APN\')) V_PREN " + " WHERE V_SUPPY.CODE IS NOT NULL  AND V_SUPPY.CODE=V_SALE.CODE(+) AND V_SUPPY.CODE=V_EXP.CODE(+) AND V_SUPPY.CODE = V_INV.CODE(+) AND V_SUPPY.CODE = V_JUNK.CODE(+) AND (SALE_QTY <> 0 OR INV_QTY<> 0 OR JUNK_QTY<>0 OR EXP_INV_QTY<>0 OR EXP_JUNK_QTY<>0) " + " AND V_SUPPY.CODE = V_BP.CODE(+) AND V_SUPPY.CODE = V_ST.CODE(+) AND V_SUPPY.CODE = V_PREN.CODE(+) ";
				
				//Dim SqlCommandCount = "SELECT COUNT (A.CODE) as row_total_count  FROM " _
				//                   + " (SELECT A.CODE,NVL(SUM(A.SALE_QTY_UPD),0)SALE_QTY  " _
				//                   + " FROM VND_INVENTORY_SUMMARY A WHERE  A.DEPT_DATE BETWEEN '" & Me.txtDEPT_DATE1.Text & "' AND '" & Me.txtDEPT_DATE2.Text & "' GROUP BY A.CODE)A  " _
				//                   + " ,(SELECT A.CODE,A.ITEM_NAME,B.INVENTORY_QTY FROM  " _
				//                   + " (SELECT A.CODE,DECODE(B.ENGLISH_SHORTNAME,null,B.CHINESE_SHORTNAME,B.ENGLISH_SHORTNAME) ITEM_NAME FROM " _
				//                   + " (SELECT CODE FROM VND_SUPPLY WHERE VENDOR_NO='" & LoginUser.VENDOR_NO & "')A,BAS_MATERIAL B WHERE " _
				//                   + " A.CODE=B.CODE(+) and b.material_type='S'  " _
				//                   + " and b.code not in (select code from BAS_MATERIAL where VENDOR_NO='" & LoginUser.VENDOR_NO & "'and  class='G' and subclass='7'))A, " _
				//                   + " (SELECT CODE,NVL(SUM(ABS(CURR_QTY)),0) AS INVENTORY_QTY FROM INV_INVENTORY where store_no in(select store_no from BAS_STORE where store_type='1' and sign_flag='Y') GROUP BY CODE)B " _
				//                   + " WHERE A.CODE=B.CODE(+))B WHERE B.CODE=A.CODE(+) AND A.CODE IS NOT NULL "
				
				//20111222,MODI NEW CODE
				//Dim SqlCommandCount = "SELECT COUNT(*) as row_total_count FROM ("
				//SqlCommandCount += SqlCommandText
				
				//20111222,MODI WALTER,BK OLD CODE
				//string SqlCommandCount = "SELECT COUNT(V_SUPPY.CODE) as row_total_count FROM " + "(SELECT A.CODE,DECODE(B.ENGLISH_SHORTNAME,null,B.CHINESE_SHORTNAME,B.ENGLISH_SHORTNAME) ITEM_NAME FROM (SELECT CODE FROM VND_SUPPLY WHERE VENDOR_NO=\'" + LoginUser.VENDOR_NO + "\')A,BAS_MATERIAL B WHERE B.MATERIAL_TYPE=\'S\' AND B.CODE NOT IN (SELECT CODE FROM BAS_MATERIAL WHERE VENDOR_NO=\'" + LoginUser.VENDOR_NO + "\' AND  CLASS=\'G\' AND SUBCLASS=\'7\') and A.CODE=B.CODE(+)) V_SUPPY, " + "(SELECT A.CODE,NVL(SUM(A.SALE_QTY_UPD),0)SALE_QTY FROM VND_INVENTORY_SUMMARY A WHERE  A.DEPT_DATE BETWEEN \'" + this.txtDEPT_DATE1.Text + "\' AND \'" + this.txtDEPT_DATE2.Text + "\' GROUP BY A.CODE)V_SALE , " + "(SELECT CODE,EXP_INV_QTY,EXP_JUNK_QTY FROM VND_INVENTORY_SUMMARY WHERE DEPT_DATE=\'" + this.txtDEPT_DATE2.Text + "\') V_EXP, " + "(SELECT CODE,NVL(SUM(ABS(CURR_QTY)),0) AS INV_QTY FROM INV_INVENTORY WHERE STORE_NO <> ALL(\'TEMG\',\'TEMO\',\'STOR\',\'AA\',\'UNI\') GROUP BY CODE) V_INV, " + "(SELECT CODE,NVL(SUM(ABS(CURR_QTY)),0) AS JUNK_QTY FROM INV_INVENTORY WHERE STORE_NO IN (\'TEMG\',\'TEMO\', \'STOR\') GROUP BY CODE) V_JUNK " + ",(SELECT D.CODE,SUM(DECODE(CARD_ID,\'B\',TRANS_QTY ,\'P\',TRANS_QTY,\'J\',-1*TRANS_QTY,-1*TRANS_QTY)) TRANS_QTY FROM (SELECT * FROM INV_TRANS_MASTER WHERE CONFIRM_DATE >=\'" + this.txtDEPT_DATE1.Text + "\' AND CONFIRM_DATE <=\'" + this.txtDEPT_DATE2.Text + "\' AND CARD_ID IN (\'B\',\'P\',\'J\',\'Q\')  AND STORE_FROM IN (\'MANO\',\'MANG\')) M LEFT JOIN INV_TRANS_DETAILS D ON M.TRANS_NO=D.TRANS_NO GROUP BY D.CODE) V_BP " + ",(SELECT D.CODE,SUM(DECODE(CARD_ID,\'G\',TRANS_QTY ,-1*TRANS_QTY)) TRANS_QTY FROM (SELECT * FROM INV_TRANS_MASTER WHERE CONFIRM_DATE >=\'" + this.txtDEPT_DATE1.Text + "\' AND CONFIRM_DATE <=\'" + this.txtDEPT_DATE2.Text + "\' AND CARD_ID IN (\'G\')  AND STORE_FROM IN (\'APN\')   AND REMARK LIKE \'短少報告書%\') M LEFT JOIN INV_TRANS_DETAILS D ON M.TRANS_NO=D.TRANS_NO GROUP BY CODE) V_ST " + ",(SELECT CODE,PREN_QTY FROM INV_INVENTORY WHERE STORE_NO IN (\'APN\')) V_PREN " + " WHERE V_SUPPY.CODE IS NOT NULL  AND V_SUPPY.CODE=V_SALE.CODE(+) AND V_SUPPY.CODE=V_EXP.CODE(+) AND V_SUPPY.CODE = V_INV.CODE(+) AND V_SUPPY.CODE = V_JUNK.CODE(+) AND (SALE_QTY <> 0 OR INV_QTY<> 0 OR JUNK_QTY<>0 OR EXP_INV_QTY<>0 OR EXP_JUNK_QTY<>0) "
				//+ " AND V_SUPPY.CODE = V_BP.CODE(+) AND V_SUPPY.CODE = V_ST.CODE(+) AND V_SUPPY.CODE = V_PREN.CODE(+) ";
				
				//Ken
                string SqlCommandCount = "SELECT COUNT(V_SUPPY.CODE) as row_total_count FROM " + "(SELECT A.CODE,DECODE(B.ENGLISH_SHORTNAME,null,B.CHINESE_SHORTNAME,B.ENGLISH_SHORTNAME) ITEM_NAME FROM (SELECT CODE FROM VND_SUPPLY WHERE VENDOR_NO=:VENDORNO)A,BAS_MATERIAL B WHERE B.MATERIAL_TYPE=\'S\' AND B.CODE NOT IN (SELECT CODE FROM BAS_MATERIAL WHERE VENDOR_NO=:VENDORNO AND  CLASS=\'G\' AND SUBCLASS=\'7\') and A.CODE=B.CODE(+)) V_SUPPY, " + "(SELECT A.CODE,NVL(SUM(A.SALE_QTY_UPD),0)SALE_QTY FROM VND_INVENTORY_SUMMARY A WHERE  A.DEPT_DATE BETWEEN :DEPTDATE1 AND :DEPTDATE2 GROUP BY A.CODE)V_SALE , " + "(SELECT CODE,EXP_INV_QTY,EXP_JUNK_QTY FROM VND_INVENTORY_SUMMARY WHERE DEPT_DATE=:DEPTDATE2) V_EXP, " + "(SELECT CODE,NVL(SUM(ABS(CURR_QTY)),0) AS INV_QTY FROM INV_INVENTORY WHERE STORE_NO <> ALL(\'TEMG\',\'TEMO\',\'STOR\',\'AA\',\'UNI\') GROUP BY CODE) V_INV, " + "(SELECT CODE,NVL(SUM(ABS(CURR_QTY)),0) AS JUNK_QTY FROM INV_INVENTORY WHERE STORE_NO IN (\'TEMG\',\'TEMO\', \'STOR\') GROUP BY CODE) V_JUNK " + ",(SELECT D.CODE,SUM(DECODE(CARD_ID,\'B\',TRANS_QTY ,\'P\',TRANS_QTY,\'J\',-1*TRANS_QTY,-1*TRANS_QTY)) TRANS_QTY FROM (SELECT * FROM INV_TRANS_MASTER WHERE CONFIRM_DATE >=:DEPTDATE1 AND CONFIRM_DATE <=:DEPTDATE2 AND CARD_ID IN (\'B\',\'P\',\'J\',\'Q\')  AND STORE_FROM IN (\'MANO\',\'MANG\')) M LEFT JOIN INV_TRANS_DETAILS D ON M.TRANS_NO=D.TRANS_NO GROUP BY D.CODE) V_BP " + ",(SELECT D.CODE,SUM(DECODE(CARD_ID,\'G\',TRANS_QTY ,-1*TRANS_QTY)) TRANS_QTY FROM (SELECT * FROM INV_TRANS_MASTER WHERE CONFIRM_DATE >=:DEPTDATE1 AND CONFIRM_DATE <=:DEPTDATE2 AND CARD_ID IN (\'G\')  AND STORE_FROM IN (\'APN\')   AND REMARK LIKE \'短少報告書%\') M LEFT JOIN INV_TRANS_DETAILS D ON M.TRANS_NO=D.TRANS_NO GROUP BY CODE) V_ST " + ",(SELECT CODE,PREN_QTY FROM INV_INVENTORY WHERE STORE_NO IN (\'APN\')) V_PREN " + " WHERE V_SUPPY.CODE IS NOT NULL  AND V_SUPPY.CODE=V_SALE.CODE(+) AND V_SUPPY.CODE=V_EXP.CODE(+) AND V_SUPPY.CODE = V_INV.CODE(+) AND V_SUPPY.CODE = V_JUNK.CODE(+) AND (SALE_QTY <> 0 OR INV_QTY<> 0 OR JUNK_QTY<>0 OR EXP_INV_QTY<>0 OR EXP_JUNK_QTY<>0) "
                + " AND V_SUPPY.CODE = V_BP.CODE(+) AND V_SUPPY.CODE = V_ST.CODE(+) AND V_SUPPY.CODE = V_PREN.CODE(+) ";
				
				//組合條件字串
				str_SQL_a[0] = txtCODE1.Text.Replace(",", "\'\'");
				//str_SQL_b(0) = String.Format(" V_SUPPY.CODE3 between  '{0}' and '{1}' and ", str_SQL_a(0), txtCODE2.Text.Replace(",", "''"))
				//str_SQL_b[0] = string.Format(" V_SUPPY.CODE between  \'{0}\' and \'{1}\' and ", str_SQL_a[0], txtCODE2.Text.Replace(",", "\'\'"));
				//Ken
                str_SQL_b[0] = " V_SUPPY.CODE between  :SQLA and :CODE2 and ";
                //查詢條件組合
				for (i = 0; i <= count - 1; i++)
				{
					if (str_SQL_a[i].Trim() != "")
					{
						strCondition = strCondition + str_SQL_b[i];
					}
				}
				
				if (!String.IsNullOrEmpty(strCondition))
				{
					strCondition = " and  " + strCondition.Substring(0, strCondition.Length - 5);
					// strCondition = " where VENDOR_NO='" & LoginUser.VENDOR_NO & "'" + strCondition.Substring(0, strCondition.Length - 3)
					
				}
				
				int intCount = 0;
				int size = PageSize; // Update by titan 由BasePage設定
                m_arrParam.Add(dba.GetOracleParam(":VENDORNO",LoginUser.VENDOR_NO));
                m_arrParam.Add(dba.GetOracleParam(":DEPTDATE1", this.txtDEPT_DATE1.Text));
                m_arrParam.Add(dba.GetOracleParam(":DEPTDATE2", this.txtDEPT_DATE2.Text));
                if (str_SQL_a[0].Trim() != "")
                {
                    m_arrParam.Add(dba.GetOracleParam(":SQLA",str_SQL_a[0]));
                    m_arrParam.Add(dba.GetOracleParam(":CODE2", txtCODE2.Text.Replace(",", "\'\'")));
                }

				// 計算資料總筆數
				//Dim dsCount As DataSet = dba.runExecuteQuery("Vendor_Count", SqlCommandCount & strCondition & ")")
				//DataSet dsCount = dba.runExecuteQuery("Vendor_Count", SqlCommandCount + strCondition);
                //使用新物件OracleADO
                DataSet dsCount = dba.runExecuteQuery("Vendor_Count", SqlCommandCount + strCondition, m_arrParam);
				if (dsCount.Tables[0].Rows[0]["ReturnCode"] == "000")
				{
					intCount = int.Parse(dsCount.Tables["Vendor_Count"].Rows[0]["row_total_count"].ToString());
					PageCount.Text = System.Convert.ToString(Math.Ceiling(double.Parse(intCount.ToString()) / size));
				}
				
				//Update By Titan 處理當總頁次小於目前頁次
				if ((int.Parse(PageCount.Text) < int.Parse(PageIndex.Text)) && int.Parse(PageCount.Text) > 0)
				{
					// 總筆數(intCount)小於一頁，從第0筆開始顯示
					PageIndex.Text = Server.HtmlEncode(PageCount.Text);
					GotoPage.Text = Server.HtmlEncode(PageCount.Text);
				}
				
				// Update by titan 由BasePage設定
				// absolute position
				int position = ((int.Parse(PageIndex.Text)) - 1) * size;
				int maxrecord = position + size;
				
				
				//控制分頁按鈕
				ControlPageButton(DataGrid_foot);
				
				//執行資料庫語法
				//ds = dba.runExecuteQuery("VND_SUPPLY", SqlCommandText + strCondition + " ORDER BY V_SUPPY.CODE3", size, position)
				//ds = dba.runExecuteQuery("VND_SUPPLY", SqlCommandText + strCondition + " ORDER BY CODE3", size, position);
                ds = dba.runExecuteQuery("VND_SUPPLY", SqlCommandText + strCondition + " ORDER BY CODE3", position, size,m_arrParam);
				
				if (!(ds == null) && ds.Tables["info"].Rows[0]["ReturnCode"] != "100" && ds.Tables["info"].Rows[0]["ReturnCode"] != "999")
				{
					ArrayList ArrKey = new ArrayList();
					DataGrid_Bind(DataGrid, ArrKey, ds.Tables["VND_SUPPLY"]);
				}
				else
				{
					showException("查無搜尋資料!");
				}
				
			}
			catch (Exception ex)
			{
				throw (ex);
			}
			finally
			{
				ds.Dispose();
			}
		}
		
		
		private void BtnNext_Click(System.Object sender, System.Web.UI.ImageClickEventArgs e)
		{
			try
			{
				PageIndex.Text = ((int.Parse(PageIndex.Text)) + 1).ToString();
				DataGrid_DataBind();
			}
			catch (Exception ex)
			{
				showException(ex.Message);
			}
		}
		
		private void btnPREV_Click(System.Object sender, System.Web.UI.ImageClickEventArgs e)
		{
			try
			{
				PageIndex.Text = ((int.Parse(PageIndex.Text)) - 1).ToString();
				DataGrid_DataBind();
			}
			catch (Exception ex)
			{
				showException(ex.Message);
			}
		}
		
		private void BtnGo_Click(System.Object sender, System.Web.UI.ImageClickEventArgs e)
		{
			ResourceManager rm = GetResourceManager();
			try
			{

				PageIndex.Text = Server.HtmlEncode(GotoPage.Text);

				DataGrid_DataBind();
			}
			catch (Exception)
			{
				showException(rm.GetString("MSG057"));
			}
		}
		
		private void btnSearch_Click(System.Object sender, System.EventArgs e)
		{
			try
			{
				PageIndex.Text = System.Convert.ToString(1);
				DataGrid_DataBind();
			}
			catch (Exception ex)
			{
				showException(ex.Message);
			}
		}
	}
	
}
